<?php

class CustProductSite extends AppModel {
    const STOCK_OUT_TYPE_FIFO = 'F';
    const STOCK_OUT_TYPE_LATEST_PRICE = 'L';

    var $name = 'CustProductSite';
    var $useTable = 'cust_products_sites';
    var $belongsTo = array(
        'Product', 'Supplier',
        'Site' => array(
            'fields' => array('site_no', 'name')
        ),
        'Customer' => array(
            'fields' => array('cust_no', 'name')
        ),
        'User' => array(
            'foreignKey' => 'collector_id',
            'fields' => array('name')
        ),
        'ShortestPeriod' => array(
            'className' => 'Period',
            'foreignKey' => 'shortest_life_cycle_period_id',
            'fields' => array('title')
        ),
        'MaintenancePeriod' => array(
            'className' => 'Period',
            'foreignKey' => 'maintenance_plan_period_id',
            'fields' => array('title')
        ),
        'CyclePeriod' => array(
            'className' => 'Period',
            'foreignKey' => 'cycle_time_period_id',
            'fields' => array('title')
        ),
        'CustStockOut'
    );
    /**
     * ใช้สำหรับ Next Location เท่านั้น
     * @var <type>
     */
    var $hasMany = array(
        'ProductSerial' => array(
            'foreignKey' => 'cust_product_site_id',
            'conditions' => array(
                'cust_stock_in_id !=' => null,
                'cust_stock_out_id' => null,
                'cust_reserve_id' => null,
                'is_active_flag' => true
            ),
            'order' => 'stock_out_priority'
        ),
        'CPSPriceHistory'
    );
    var $validate = array(
        'product_id' => array(
            'rule' => array('notEmpty'),
            'message' => 'product id is required.',
        ),
        'cust_product_id' => array(
            'rule' => array('notEmpty'),
            'message' => 'Cust product id is required.',
        ),
        'stock_code' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                'message' => 'Stock code is required.',
                'required' => true,
            ),
        ),
        'customer_id' => array(
            'rule' => array('notEmpty'),
            'message' => 'Customer is required.',
        ),
        'site_id' => array(
            'rule' => array('notEmpty'),
            'message' => 'Site is required.',
        ),
        'dispatch_from' => array(
            'rule' => array('notEmpty'),
            'message' => 'Please select a Vendor to dispatch this product.',
        ),
        'temp' => array(
            'rule' => 'numeric',
            'message' => 'Temperature must be a number.',
            'allowEmpty' => true
        ),
        'pressure' => array(
            'rule' => 'numeric',
            'message' => 'Pressure must be a number.',
            'allowEmpty' => true
        ),
    );

    /**
     * เพิ่มจำนวน current_stock ของ CustProductSite id ที่ระบุ
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่เพิ่ม
     * @return boolean true ถ้า update สำเร็จ
     */
    function addCurrentStock($id, $qty) {
        $this->id = $id;
        $this->saveField('last_stock_in_date', date('Y-m-d'));
        $this->saveField('replenish_date', null);
        return $this->_updateCurrentStock($id, $qty);
    }

    /**
     * ลดจำนวน current_stock ของ CustProductSite id ที่ระบุ
     * update next location ของ CPS นี้โดยดูจาก ProductSerial
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่ลด
     * @return boolean true ถ้า update สำเร็จ
     */
    function removeCurrentStock($id, $qty) {
        $this->id = $id;
        $this->saveField('last_stock_out_date', date('Y-m-d'));
        return $this->_updateCurrentStock($id, -$qty);
    }

    /**
     * แก้จำนวน current_stock ของ CustProductSite id ที่ระบุ
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่ลด
     * @return boolean true ถ้า update สำเร็จ
     */
    function _updateCurrentStock($id, $qty) {
        $is_success = true;
        $this->id = $id;
        $current_stock = $this->field('current_stock') + $qty;
        $is_success &= $this->saveField('current_stock', $current_stock);

        $this->_validateStock();

        return true;
    }

    /**
     * ลดจำนวน current_stock มาใส่ reserve_qty
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่ลด
     * @return boolean true ถ้า update สำเร็จ
     */
    function addReserveQty($id, $qty) {
        $this->id = $id;
        $this->_updateCurrentStock($id, -$qty);
        return $this->_updateReserveQty($id, $qty);
    }

    /**
     * ลดจำนวน reserve_qty (มีการ stock out ออกไป)
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่ลด
     * @return boolean true ถ้า update สำเร็จ
     */
    function stockOutReserveQty($id, $qty) {
        $this->id = $id;
        return $this->_updateReserveQty($id, -$qty);
    }

    /**
     * ลดจำนวน reserve_qty กลับเข้า Current stock
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่ลด
     * @return boolean true ถ้า update สำเร็จ
     */
    function returnReserveQty($id, $qty) {
        $this->id = $id;
        $this->_updateCurrentStock($id, $qty);
        return $this->_updateReserveQty($id, -$qty);
    }

    /**
     * update จำนวน reserv qty ตามที่ระบุ
     * @param int $id CustProductSite->id
     * @param int $qty จำนวนที่ลด
     * @return boolean true ถ้า update สำเร็จ
     */
    function _updateReserveQty($id, $qty) {
        $is_success = true;
        $this->id = $id;
        $reserve_qty = $this->field('reserve_qty') + $qty;
        $is_success &= $this->saveField('reserve_qty', $reserve_qty);

        return $is_success;
    }

    /**
     * ตรวจสอบ current_stock ว่า ต่ำกว่า min_stock หรือ max_stock หรือไม่
     * - ถ้าต่ำกว่าบันทึกวันที่เริ่มต่ำกว่าเกณฑ์ min_stock_date หรือ max_stock_date
     * - ถ้าไม่ต่ำกว่า clear min_stock_date หรือ max_stock_date ให้เป็น null
     * - ถ้าต่ำกว่า max_stock ให้ลบ field max_stock_note
     * ตาม CustProductSite->id ที่กำลังชี้อยู่
     */
    function _validateStock() {
        if ($this->id !== false) {
            $current_stock = $this->field('current_stock');
            $min_stock = $this->field('min_stock');
            $min_stock_date = $this->field('min_stock_date');
            $max_stock = $this->field('max_stock');
            $max_stock_date = $this->field('max_stock_date');

            // ถ้าเพิ่งเริ่มจะน้อยกว่า min_stock
            if (($current_stock <= $min_stock) && $min_stock_date == null) {
                $this->saveField('min_stock_date', date('Y-m-d'));

                // ถ้า current_stock เริ่มมากกว่า min_stock clear ค่าวันที่ min_stock_date ทิ้ง
            } elseif ($current_stock > $min_stock) {
                $this->saveField('min_stock_date', null);
            }

            // ถ้าเพิ่งเริ่มจะน้อยกว่า max_stock
            if (($current_stock < $max_stock) && $max_stock_date == null) {
                $this->saveField('max_stock_date', date('Y-m-d'));
                $this->saveField('min_stock_note', null);


                // ถ้า current_stock เริ่มมากกว่า max_stock clear ค่าวันที่ max_stock_date ทิ้ง
            } elseif ($current_stock >= $max_stock) {
                $this->saveField('max_stock_date', null);
            }
        }
    }

    /**
     * จัดการ format field เกี่ยวกับวันที่
     * @param <type> $results
     * @return <type>
     */
    function afterFind($results) {
        if (!empty($results)) {

            foreach ($results as &$cps) {
                if (isset($cps['CustProductSite'])) {
                    if (isset($cps['CustProductSite']['min_stock_date'])) {
                        $cps['CustProductSite']['min_stock_date'] = $this->dateFormatAfterFind($cps['CustProductSite']['min_stock_date']);
                    }
                    if (isset($cps['CustProductSite']['max_stock_date'])) {
                        $cps['CustProductSite']['max_stock_date'] = $this->dateFormatAfterFind($cps['CustProductSite']['max_stock_date']);
                    }
                    if (isset($cps['CustProductSite']['last_stock_in_date'])) {
                        $cps['CustProductSite']['last_stock_in_date'] = $this->dateFormatAfterFind($cps['CustProductSite']['last_stock_in_date']);
                    }
                    if (isset($cps['CustProductSite']['last_stock_out_date'])) {
                        $cps['CustProductSite']['last_stock_out_date'] = $this->dateFormatAfterFind($cps['CustProductSite']['last_stock_out_date']);
                    }
                    if (isset($cps['CustProductSite']['replenish_date'])) {
                        $cps['CustProductSite']['replenish_date'] = $this->dateFormatAfterFind($cps['CustProductSite']['replenish_date']);
                    }
                }
            }
        }
        return $results;
    }

    function beforeSave() {
        // Insert
        if (isset($this->data['CustProductSite']['replenish_date'])) {
            $this->data['CustProductSite']['replenish_date'] = $this->dateFormatBeforeSave($this->data['CustProductSite']['replenish_date']);
        }
        if (isset($this->data['CustProductSite']['price'])) {
            $this->data['CustProductSite']['price'] = str_replace(',', '', $this->data['CustProductSite']['price']);
        }
        return true;
    }

    /**
     * list customer_id จาก ลูกค้าที่มี CustProductSite เป็น Supplier ที่ระบุ
     * @param int $supplier_id
     * @return array
     */
    function listCustomerBySupplierId($supplier_id) {
        // ดึงรายการ Customer ทั้งหมด ที่มี Product ของ Supplier นี้
        $CPSs = $this->query(
                        "SELECT DISTINCT customer_id
                FROM pre_cust_products_sites cps JOIN pre_products p ON (cps.product_id = p.id)
                WHERE cps.supplier_id = {$supplier_id}
                AND   cps.dispatch_from = 'supplier'"
        );
        $customers = array();
        if ($CPSs != null) {
            foreach ($CPSs as $k => $cps) {
                foreach ($cps as $k => $v) {
                    $customers[] = $v['customer_id'];
                }
            }
        }
        return $customers;
    }

    /**
     * list supplier จาก ลูกค้าที่มี CustProductSite เป็น Customer ที่ระบุ
     * @param int $customer_id
     * @return array
     */
    function listSupplierByCustomerId($customer_id = null) {
        // ดึงรายการ Customer ทั้งหมด ที่มี Product ของ Supplier นี้
        if ($customer_id == null) {
            $CPSs = $this->query(
                            "SELECT DISTINCT supplier_id
                    FROM pre_cust_products_sites cps "
            );
        } else {
            $CPSs = $this->query(
                            "SELECT DISTINCT supplier_id
                    FROM pre_cust_products_sites cps
                    WHERE cps.customer_id = {$customer_id}"
            );
        }
        $suppliers = array();
        if (!empty($CPSs)) {

            foreach ($CPSs as $k => $cps) {
                foreach ($cps as $k => $v) {
                    $suppliers[] = $v['supplier_id'];
                }
            }
        }
        return $suppliers;
    }

    /**
     * list brand ที่ dispatch มาจาก ITS จาก ลูกค้าที่มี CustProductSite เป็น Customer ที่ระบุ
     * @param int $customer_id
     * @return array
     */
    function listItsBrandByCustomerId($customer_id) {
        // ดึงรายการ Customer ทั้งหมด ที่มี Product ของ Supplier นี้
        $CPSs = $this->query(
                        "SELECT DISTINCT b.id brand_id
                         FROM pre_cust_products_sites cps JOIN pre_brands b ON (cps.supplier_id = b.supplier_id)
                         WHERE cps.customer_id = {$customer_id}
                         AND dispatch_from = 'its'"
        );

        $brands = array();

        if (!empty($CPSs)) {
            foreach ($CPSs as $k => $cps) {
                foreach ($cps as $k => $v) {
                    $brands[] = $v['brand_id'];
                }
            }
        }
        return $brands;
    }

    /**
     * list customer_id จาก ลูกค้าที่มี CustProductSite เป็น Supplier ที่ระบุ และมี current_stock < max_stock
     * @param int $supplier_id
     * @return array
     */
    function listAlertCustomerBySupplierId($supplier_id) {
        // ดึงรายการ Customer ทั้งหมด ที่มี Product ของ Supplier นี้
        $CPSs = $this->query(
                        "SELECT DISTINCT customer_id
                FROM pre_cust_products_sites cps JOIN pre_products p ON (cps.product_id = p.id)
                WHERE p.supplier_id = {$supplier_id}
                AND   cps.dispatch_from = 'supplier'
                AND (cps.current_stock < cps.max_stock
                OR  cps.current_stock >= cps.max_stock
                OR  cps.current_stock < cps.min_stock)
                "
        );
        $customers = array();
        foreach ($CPSs as $k => $cps) {
            foreach ($cps as $k => $v) {
                $customers[] = $v['customer_id'];
            }
        }
        return $customers;
    }

    /**
     * list site_id จาก ลูกค้าที่มี CustProductSite เป็น Supplier ที่ระบุ
     * @param int $supplier_id
     * @return array
     */
    function listSiteBySupplierId($supplier_id, $customer_id) {
        // ดึงรายการ Customer ทั้งหมด ที่มี Product ของ Supplier นี้
        $CPSs = $this->query(
                        "SELECT DISTINCT site_id
                FROM pre_cust_products_sites cps JOIN pre_products p ON (cps.product_id = p.id)
                WHERE p.supplier_id = {$supplier_id}
                AND   cps.customer_id = {$customer_id}
                AND   cps.dispatch_from = 'supplier'
                "
        );
        $data = array();
        if ($CPSs != null) {
            foreach ($CPSs as $k => $cps) {
                foreach ($cps as $k => $v) {
                    $data[] = $v['site_id'];
                }
            }
        }
        return $data;
    }

    /**
     * list product_id จาก ทั้งหมดที่ customer นี้มี
     * @param int $customer_id
     * @return array
     */
    function listProductIdByCustomerId($customer_id, $conditions =null) {
        $sql = "SELECT DISTINCT product_id FROM pre_cust_products_sites cps JOIN pre_products p ON (cps.product_id = p.id) WHERE customer_id = {$customer_id} ";
        if (isset($conditions)) {

            if (isset($conditions['stock_code'])) {
                $sql .= " AND stock_code LIKE '%{$conditions['stock_code']}%'";
            }
            if (isset($conditions['dispatch_from'])) {
                $sql .= " AND dispatch_from='{$conditions['dispatch_from']}'";
            }
            if (isset($conditions['supplier_id'])) {
                $sql .= " AND cps.supplier_id='{$conditions['supplier_id']}'";
            }
//        if (isset($conditions['title'])) {
//            $sql .= " AND (cps.text IS NOT NULL AND cps.text LIKE '%{$conditions['title']}%') OR (cps.text IS NULL AND p.title LIKE '%{$conditions['title']}%')";
//        }
        }
        $CPSs = $this->query($sql);
        $data = array();
        foreach ($CPSs as $k => $cps) {
            foreach ($cps as $k => $v) {
                $data[] = $v['product_id'];
            }
        }
        return $data;
    }

    function listProductIdBySiteId($site_id, $dispatch_from = null) {
        $sql = "SELECT DISTINCT product_id FROM pre_cust_products_sites cps
                WHERE site_id = {$site_id} ";

        if ($dispatch_from != null) {
            $sql .= " AND dispatch_from = '{$dispatch_from}'";
        }

        $CPSs = $this->query($sql);
        $data = array();
        foreach ($CPSs as $k => $cps) {
            foreach ($cps as $k => $v) {
                $data[] = $v['product_id'];
            }
        }
        return $data;
    }

    function afterSave($created) {
        // inserted
        if ($created) {
            $this->CPSPriceHistory->save($this->id, $this->data['CustProductSite']['price'], $this->data['CustProductSite']['collector_id']);
        } else {
            // มีการ update ราคา
            if (!empty($this->data['CustProductSite']['price'])) {
                //updated
                $cpsPriceHistory = $this->CPSPriceHistory->find('first', array(
                            'conditions' => array(
                                'cust_product_site_id' => $this->id
                            ),
                            'order' => 'created DESC',
                            'contain' => array()
                        ));
                // มีการ update ราคา ราคาใน CPS ล่าสุด ไม่เท่ากับใน history
                if ($cpsPriceHistory != null || ($cpsPriceHistory['CPSPriceHistory']['price'] != $this->field('price'))) {
                    $this->CPSPriceHistory->save($this->id, $this->data['CustProductSite']['price'], $this->field('collector_id'));
                }
            }
        }
    }

    function reportCustStocksIn($site_id, $data) {
        $display_stock_in_no = $data['Option']['display_stock_in_no'];
        $sql = "SELECT CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from,
                        CustStockInHead.id, CustStockInHead.stock_in_no, DATE_FORMAT(stock_in_date, '%d %M %Y') stock_in_date, CustStockInHead.dispatch_from,
                        Product.part_no, Product.supplier_part_no, Supplier.name,
                        CustStockIn.quotation_price price, SUM(stock_in_qty) stock_in_qty, COUNT(*) frequency,
                        (CASE WHEN CustProductSite.text = '' OR CustProductSite.text IS NULL THEN Product.text ELSE CustProductSite.text END) text
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_in CustStockIn ON (CustStockIn.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_in_heads CustStockInHead ON (CustStockIn.cust_stock_in_head_id = CustStockInHead.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                WHERE CustProductSite.site_id = {$site_id}
                AND CustProductSite.discontinued = 0 ";

        // Check เงื่อนไขการค้นหา
        $sql .= $this->addSqlConditions($data);

        $sql .= " GROUP BY CustProductSite.stock_code, CustStockIn.quotation_price" . ($display_stock_in_no ? ", CustStockInHead.stock_in_no " : '') . " WITH ROLLUP ";
        $result = $this->query($sql);

        $data = null;

        $cps = array();
        $csih = array();
        $i = $j = $k = 0;
        // query ได้ data เรียงจาก group ย่อยสุด มาหา group ใหญ่สุด
        // CustProductSite <= CustProductSite.price <= CustStockInHead
        // แปลง result ให้เหมือน $data ปกติ
        foreach ($result as $row) {
            // row stock in head
            if ($display_stock_in_no && !empty($row['CustStockInHead']['stock_in_no'])) {
                $csih[$k] = $row['CustStockInHead'];
                $csih[$k]['CustStockIn']['stock_in_qty'] = $row[0]['stock_in_qty'];
                $csih[$k]['CustStockIn']['stock_in_date'] = $row[0]['stock_in_date'];
                ++$k;
            } elseif (!empty($row['CustStockIn']['price'])) {
                // row stock code
                $cps['CustProductSite'] = $row['CustProductSite'];
                $cps['Product'] = $row['Product'];
                $cps['Supplier'] = $row['Supplier'];
                $cps['CustStockIn'] = $row['CustStockIn'];
                $cps[0] = $row[0];
                $cps['CustStockInHead'][0] = $row['CustStockInHead'];
                if ($display_stock_in_no) {
                    $cps['CustStockInHead'] = $csih;
                    $csih = array();
                }
                $data[$i] = $cps;

                $cps = array();
                $k = 0;
                ++$i;
            }
        }
        return $data;
    }

    function reportCustStocksOut($site_id, $data) {
        $display_stock_out = $data['Option']['display_stock_out_no'];
        $sql = "SELECT CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from,
                        CustStockOutHead.id, CustStockOutHead.stock_out_no, stock_out_date, CustStockOutHead.dispatch_from, CustStockOutHead.remark,
                        Product.part_no, Product.supplier_part_no, Supplier.name,
                        CustStockOut.stock_out_avg_price price, SUM(stock_out_qty) stock_out_qty, SUM(return_qty) return_qty, SUM(net_stock_out_qty) net_stock_out_qty, COUNT(*) frequency,
                        (CASE WHEN CustProductSite.text = '' OR CustProductSite.text IS NULL THEN Product.text ELSE CustProductSite.text END) text,
                        SiteCostCenter.name
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                LEFT JOIN pre_site_cost_centers SiteCostCenter ON (CustStockOutHead.site_cost_center_id = SiteCostCenter.id)
                WHERE CustProductSite.site_id = {$site_id}
                AND CustProductSite.discontinued = 0 ";

        // Check เงื่อนไขการค้นหา
        $sql .= $this->addSqlConditions($data);

        $sql .= " GROUP BY CustProductSite.stock_code, CustStockOut.stock_out_avg_price" . ($display_stock_out ? ", CustStockOutHead.stock_out_no " : '') . " WITH ROLLUP ";
        $result = $this->query($sql);

//        debug($result);
        $data = null;

        $cps = array();
        $csih = array();
        $i = $j = $k = 0;
        // query ได้ data เรียงจาก group ย่อยสุด มาหา group ใหญ่สุด
        // CustProductSite <= CustProductSite.price <= CustStockOutHead
        // แปลง result ให้เหมือน $data ปกติ
        foreach ($result as $row) {
            // row stock in head
            if ($display_stock_out && !empty($row['CustStockOutHead']['stock_out_no'])) {
                $csih[$k] = $row['CustStockOutHead'];
                $csih[$k]['CustStockOut'] = $row[0];
                $csih[$k]['SiteCostCenter'] = $row['SiteCostCenter'];
                ++$k;
            } elseif (!empty($row['CustStockOut']['price'])) {
                // row stock code
                $cps['CustProductSite'] = $row['CustProductSite'];
                $cps['Product'] = $row['Product'];
                $cps['Supplier'] = $row['Supplier'];
                $cps['CustStockOut'] = $row['CustStockOut'];
                $cps[0] = $row[0];
                $cps['CustStockOutHead'][0] = $row['CustStockOutHead'];
                if ($display_stock_out) {
                    $cps['CustStockOutHead'] = $csih;
                    $csih = array();
                }
                $data[$i] = $cps;
                $cps = array();
                $k = 0;
                ++$i;
            }
        }
        return $data;
    }

    /**
     * สำหรับเรียก Report Customer Stock out จากหน้าจอ Vendor Group by Part
     * @param <type> $supplier_id
     * @param <type> $data array conditions
     * @return <type>
     */
    function reportCustStocksOutBySupplier($supplier_id, $data) {
        $display_stock_out = $data['Option']['display_stock_out_no'];
        $sql = "SELECT Product.id, CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from, CustStockOut.stock_out_avg_price price,
                       CustStockOutHead.id, CustStockOutHead.dispatch_from, stock_out_no, stock_out_date, remark,
                       Product.part_no, Product.supplier_part_no, Supplier.name, Product.text, CustProductSite.text,
                       (CASE WHEN CustProductSite.text = '' OR CustProductSite.text IS NULL THEN Product.text ELSE CustProductSite.text END) text,
                       SUM(stock_out_qty) stock_out_qty, SUM(return_qty) return_qty, SUM(net_stock_out_qty) net_stock_out_qty, COUNT(*) frequency,
                       Customer.name, Site.name
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
                JOIN pre_customers Customer ON (Customer.id = CustProductSite.customer_id)
                JOIN pre_sites Site ON (Site.id = CustProductSite.site_id)
                WHERE CustProductSite.discontinued = 0
                AND CustStockOutHead.supplier_id = {$supplier_id} ";

        // Check เงื่อนไขการค้นหา
        $sql .= $this->addSqlConditions($data);

        $sql .= " GROUP BY CustProductSite.stock_code, CustStockOut.stock_out_avg_price" . ($display_stock_out ? ", CustStockOutHead.stock_out_no " : '') . " WITH ROLLUP ";
        $result = $this->query($sql);

//        debug($result);
        $data = null;

        $cps = array();
        $csih = array();
        $i = $j = $k = 0;
        // query ได้ data เรียงจาก group ย่อยสุด มาหา group ใหญ่สุด
        // CustProductSite <= CustProductSite.price <= CustStockOutHead
        // แปลง result ให้เหมือน $data ปกติ
        foreach ($result as $row) {
            // row stock in head
            if ($display_stock_out && !empty($row['CustStockOutHead']['stock_out_no'])) {
                $csih[$k] = $row['CustStockOutHead'];
                $csih[$k]['CustStockOut'] = $row[0];
                ++$k;
            } elseif (!empty($row['CustStockOut']['price'])) {
                // row stock code
                $cps['CustProductSite'] = $row['CustProductSite'];
                $cps['Product'] = $row['Product'];
                $cps['Supplier'] = $row['Supplier'];
                $cps['CustStockOut'] = $row['CustStockOut'];
                $cps['Customer'] = $row['Customer'];
                $cps['Site'] = $row['Site'];
                $cps[0] = $row[0];
                $cps['CustStockOutHead'][0] = $row['CustStockOutHead'];
                if ($display_stock_out) {
                    $cps['CustStockOutHead'] = $csih;
                    $csih = array();
                }
                $data[$i] = $cps;
                $cps = array();
                $k = 0;
                ++$i;
            }
        }
        return $data;
    }

    /**
     *  ลด supplier_current_stock
     *  เพิ่ม request_qty
     */
    function addRequestQty($id, $qty) {
        $this->id = $id;

        // ลด supplier_current_stock
        $supplier_cs = $this->field('supplier_current_stock');
        $this->saveField('supplier_current_stock', $supplier_cs - $qty);

        // เพิ่ม reqeust_qty
        $request_qty = $this->field('request_qty');
        $this->saveField('request_qty', $request_qty + $qty);
    }

    /**
     *  เพิ่ม supplier_current_stock
     *  ลด request_qty
     */
    function reduceRequestQty($id, $qty) {
        $this->id = $id;

        // เพิ่ม supplier_current_stock
        $supplier_cs = $this->field('supplier_current_stock');
        $this->saveField('supplier_current_stock', $supplier_cs + $qty);

        // ลด reqeust_qty
        $request_qty = $this->field('request_qty');
        $this->saveField('request_qty', $request_qty - $qty);
    }

}
